{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开出库调整单\n",
    "\n",
    "wb=xw.Book(r'K:\\BaiduSyncdisk\\王振洋资料\\1.商贸公司资料\\3月商贸公司资料\\9.出库调整单\\出库调整单3月.xlsx')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 读取导出的出库调整单数据\n",
    "\n",
    "df=wb.sheets('出库调整单').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "\n",
    "# 去除需要使用的列\n",
    "df=df.loc[:,['仓库（表体）','存货名称','调整金额']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基础数据表\n",
    "\n",
    "\n",
    "# 打开t+基础数据表并写入相关数据，为匹配做准备\n",
    "wb基础数据=xw.Book(r't+基础数据.xlsx')\n",
    "仓库档案=wb基础数据.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','成本科目编码','成本科目名称']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "合并=pd.merge(df,仓库档案,left_on='仓库（表体）',right_on='仓库名称',how='left')\n",
    "合并=pd.merge(合并,部门档案,left_on='仓库（表体）',right_on='部门',how='left')\n",
    "合并=pd.merge(合并,存货档案,left_on='存货名称',right_on='存货名称',how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将合并后的数据写入到模版数据源表\n",
    "\n",
    "# 将合并写入<编码匹配中间表>\n",
    "\n",
    "# 创建一个sheet名字构建的列表，用于后续判断\n",
    "sheet_name=[wb.sheets[i].name for i in range(wb.sheets.count)]\n",
    "# 将数据写入到编码匹配中间表，对匹配失败的数据手动补充\n",
    "if '模版数据源表' in sheet_name:\n",
    "    new_sheet=wb.sheets('模版数据源表')\n",
    "    new_sheet.cells.clear()\n",
    "    new_sheet.cells.number_format = '@'\n",
    "    new_sheet.range('a1').value=合并\n",
    "else:\n",
    "    new_sheet=wb.sheets.add('模版数据源表')\n",
    "    new_sheet.cells.number_format = '@'\n",
    "    new_sheet.range('a1').value=合并\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将凭证导入导出移动过来\n",
    "\n",
    "凭证导入导出p=r'K:\\BaiduSyncdisk\\王振洋资料\\1.商贸公司资料\\0.凭证导入导出-商贸.xlsx'\n",
    "\n",
    "sheetname = wb.sheet_names\n",
    "\n",
    "if '凭证导入导出' not in sheetname:\n",
    "    凭证导入导出=xw.Book(凭证导入导出p)\n",
    "    凭证导入导出.sheets('凭证导入导出').copy(after=wb.sheets[wb.sheets.count-1])\n",
    "else:\n",
    "    None\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "数据源=wb.sheets('模版数据源表').range('a1').expand().options(pd.DataFrame).value\n",
    "数据源['借贷方向']='借方'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "数据源2=数据源.groupby(by=['部门编码','部门']).sum('调整金额').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "数据源2['借贷方向']='贷方'\n",
    "数据源2['成本科目编码']='1405'\n",
    "数据源2['成本科目名称'] ='库存商品'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "数据源3=pd.concat([数据源,数据源2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "数据源3['币种']='人民币'\n",
    "数据源3['原币']=数据源3['调整金额']\n",
    "数据源3['本币']=数据源3['调整金额']\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>仓库（表体）</th>\n",
       "      <th>存货名称</th>\n",
       "      <th>调整金额</th>\n",
       "      <th>仓库编码</th>\n",
       "      <th>仓库名称</th>\n",
       "      <th>部门编码</th>\n",
       "      <th>部门</th>\n",
       "      <th>存货编码</th>\n",
       "      <th>计量单位</th>\n",
       "      <th>成本科目编码</th>\n",
       "      <th>成本科目名称</th>\n",
       "      <th>借贷方向</th>\n",
       "      <th>币种</th>\n",
       "      <th>原币</th>\n",
       "      <th>本币</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.0</th>\n",
       "      <td>郑州</td>\n",
       "      <td>保温箱</td>\n",
       "      <td>-0.02</td>\n",
       "      <td>02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>01001</td>\n",
       "      <td>郑州</td>\n",
       "      <td>010102</td>\n",
       "      <td>个</td>\n",
       "      <td>64010102</td>\n",
       "      <td>保温箱</td>\n",
       "      <td>借方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>-0.02</td>\n",
       "      <td>-0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>郑州</td>\n",
       "      <td>头盔</td>\n",
       "      <td>-0.21</td>\n",
       "      <td>02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>01001</td>\n",
       "      <td>郑州</td>\n",
       "      <td>010104</td>\n",
       "      <td>个</td>\n",
       "      <td>64010104</td>\n",
       "      <td>头盔</td>\n",
       "      <td>借方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>-0.21</td>\n",
       "      <td>-0.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <td>郑州</td>\n",
       "      <td>加厚冲锋衣</td>\n",
       "      <td>0.02</td>\n",
       "      <td>02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>01001</td>\n",
       "      <td>郑州</td>\n",
       "      <td>010108</td>\n",
       "      <td>件</td>\n",
       "      <td>64010108</td>\n",
       "      <td>加厚冲锋衣</td>\n",
       "      <td>借方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <td>郑州</td>\n",
       "      <td>触屏手套</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>01001</td>\n",
       "      <td>郑州</td>\n",
       "      <td>010230</td>\n",
       "      <td>双</td>\n",
       "      <td>64010230</td>\n",
       "      <td>触屏手套</td>\n",
       "      <td>借方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>-0.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.0</th>\n",
       "      <td>郑州</td>\n",
       "      <td>头盔镜片</td>\n",
       "      <td>0.04</td>\n",
       "      <td>02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>01001</td>\n",
       "      <td>郑州</td>\n",
       "      <td>010257</td>\n",
       "      <td>个</td>\n",
       "      <td>64010257</td>\n",
       "      <td>头盔镜片</td>\n",
       "      <td>借方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1162.84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01060</td>\n",
       "      <td>淮北</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1405</td>\n",
       "      <td>库存商品</td>\n",
       "      <td>贷方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1162.84</td>\n",
       "      <td>1162.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>158.97</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01061</td>\n",
       "      <td>潍坊</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1405</td>\n",
       "      <td>库存商品</td>\n",
       "      <td>贷方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>158.97</td>\n",
       "      <td>158.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>251.85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01062</td>\n",
       "      <td>唐山</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1405</td>\n",
       "      <td>库存商品</td>\n",
       "      <td>贷方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>251.85</td>\n",
       "      <td>251.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01064</td>\n",
       "      <td>自贡</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1405</td>\n",
       "      <td>库存商品</td>\n",
       "      <td>贷方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>-0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01065</td>\n",
       "      <td>湛江</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1405</td>\n",
       "      <td>库存商品</td>\n",
       "      <td>贷方</td>\n",
       "      <td>人民币</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>209 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     仓库（表体）   存货名称     调整金额 仓库编码 仓库名称   部门编码  部门    存货编码 计量单位    成本科目编码  \\\n",
       "0.0      郑州    保温箱    -0.02   02   郑州  01001  郑州  010102    个  64010102   \n",
       "1.0      郑州     头盔    -0.21   02   郑州  01001  郑州  010104    个  64010104   \n",
       "2.0      郑州  加厚冲锋衣     0.02   02   郑州  01001  郑州  010108    件  64010108   \n",
       "3.0      郑州   触屏手套    -0.07   02   郑州  01001  郑州  010230    双  64010230   \n",
       "4.0      郑州   头盔镜片     0.04   02   郑州  01001  郑州  010257    个  64010257   \n",
       "...     ...    ...      ...  ...  ...    ...  ..     ...  ...       ...   \n",
       "46.0    NaN    NaN  1162.84  NaN  NaN  01060  淮北     NaN  NaN      1405   \n",
       "47.0    NaN    NaN   158.97  NaN  NaN  01061  潍坊     NaN  NaN      1405   \n",
       "48.0    NaN    NaN   251.85  NaN  NaN  01062  唐山     NaN  NaN      1405   \n",
       "49.0    NaN    NaN    -0.01  NaN  NaN  01064  自贡     NaN  NaN      1405   \n",
       "50.0    NaN    NaN     0.04  NaN  NaN  01065  湛江     NaN  NaN      1405   \n",
       "\n",
       "     成本科目名称 借贷方向   币种       原币       本币  \n",
       "0.0     保温箱   借方  人民币    -0.02    -0.02  \n",
       "1.0      头盔   借方  人民币    -0.21    -0.21  \n",
       "2.0   加厚冲锋衣   借方  人民币     0.02     0.02  \n",
       "3.0    触屏手套   借方  人民币    -0.07    -0.07  \n",
       "4.0    头盔镜片   借方  人民币     0.04     0.04  \n",
       "...     ...  ...  ...      ...      ...  \n",
       "46.0   库存商品   贷方  人民币  1162.84  1162.84  \n",
       "47.0   库存商品   贷方  人民币   158.97   158.97  \n",
       "48.0   库存商品   贷方  人民币   251.85   251.85  \n",
       "49.0   库存商品   贷方  人民币    -0.01    -0.01  \n",
       "50.0   库存商品   贷方  人民币     0.04     0.04  \n",
       "\n",
       "[209 rows x 15 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "数据源3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从凭证导入导出将数据写入到《凭证导入导出》\n",
    "凭证模版=wb.sheets('凭证导入导出')\n",
    "凭证模版.range('a4:ab10000').clear_contents()\n",
    "凭证模版.range('h2:j2').value=数据源3.loc[:,['成本科目编码','成本科目名称','币种']].values  \n",
    "凭证模版.range('m2:o2').value=数据源3.loc[:,['借贷方向','原币','本币']].values\n",
    "凭证模版.range('aa2:ab2').value=数据源3.loc[:,['部门编码','部门']].values\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
